﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;

namespace DbHelp {
    /// <summary>
    /// 数据库帮助类
    /// </summary>
    public abstract class DbHelper {
        protected DbHelper() {
        }
        ~DbHelper() {
            if (Connection != null && Connection.State == System.Data.ConnectionState.Open) {
                Connection.Close();
                Connection.Dispose();
            }
        }

        bool _autoClose = true;

        public bool AutoClose {
            get {
                return _autoClose;
            }
            set {
                _autoClose = value;
            }
        }

        DbProviderFactory _ProviderFactory;

        public DbProviderFactory ProviderFactory {
            get {
                return _ProviderFactory;
            }
            set {
                _ProviderFactory = value;
            }
        }

        string _ConnectionString;

        public string ConnectionString {
            get {
                return _ConnectionString;
            }
            protected set {
                _ConnectionString = value;
            }
        }

        private DbConnection _Connection;
        /// <summary>
        /// 数据连接
        /// </summary>
        public DbConnection Connection {
            get {
                if (_Connection == null) {
                    _Connection = ProviderFactory.CreateConnection();
                    _Connection.ConnectionString = ConnectionString;
                }
                return _Connection;
            }
            internal set {
                _Connection = value;
            }
        }

        public ConnectionState ConnectionState {
            get {
                return Connection.State;
            }
        }

        private DbCommand _Command;

        public DbCommand Command {
            get {
                if (_Command == null) {
                    _Command = ProviderFactory.CreateCommand();
                    _Command.Connection = Connection;
                }
                return _Command;
            }
        }

        public int ExecuteNonQuery(string sql) {
            try {
                if (Connection.State != ConnectionState.Open) {
                    Connection.Open();
                }
                Command.CommandText = sql;
                return Command.ExecuteNonQuery();
            }
            catch (Exception ex) {
                Log.Error("ERROR ExecuteNonQuery:" + ex.Message);
                throw new NotImplementedException(ex.Message);
            }
            finally {
                if (_autoClose && Connection.State != System.Data.ConnectionState.Closed) {
                    Connection.Close();
                }
            }
        }

        public DbDataReader ExecuteReader(string sql) {
            try {
                if (Connection.State != ConnectionState.Open) {
                    Connection.Open();
                }

                Command.CommandText = sql;

                if (_autoClose) {
                    return Command.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else {
                    return Command.ExecuteReader();
                }
            }
            catch (Exception ex) {
                Log.Error("ERROR ExecuteReader:" + ex.Message);
                throw new NotImplementedException(ex.Message);
            }
            finally {
                //DbConnection.Close();
            }
        }

        public object ExecuteScalar(string sql) {
            try {
                if (Connection.State != ConnectionState.Open) {
                    Connection.Open();
                }

                Command.CommandText = sql;

                return Command.ExecuteScalar();
            }
            catch (Exception ex) {
                Log.Error("ERROR ExecuteScalar:" + ex.Message);
                throw new NotImplementedException(ex.Message);
            }
            finally {
                if (_autoClose && Connection.State != System.Data.ConnectionState.Closed) {
                    Connection.Close();
                }
            }
        }
        /// <summary>
        /// 获取架构信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetSchema(string pra) {
            DataTable tab = null;
            try {
                if (Connection.State != ConnectionState.Open) {
                    Connection.Open();
                }
                if (pra == null) {
                    tab = Connection.GetSchema();
                }
                else {
                    tab = Connection.GetSchema(pra);
                }
            }
            catch (Exception ex) {
                Log.Error("GetSchema" + ex.Message);
                throw new NotImplementedException(ex.Message);
            }
            finally {
                if (_autoClose && Connection.State != System.Data.ConnectionState.Closed) {
                    Connection.Close();
                }
            }
            return tab;
        }

        public DataTable GetSchema() {
            return this.GetSchema(null);
        }

        public DataSet GetDataSet(string sql) {
            DataSet ds = new DataSet();
            try {
                if (Connection.State != ConnectionState.Open) {
                    Connection.Open();
                }
                using (DbDataAdapter da = ProviderFactory.CreateDataAdapter()) {
                    Command.CommandText = sql;
                    da.SelectCommand = Command;
                    da.Fill(ds);
                }
            }
            catch (Exception ex) {
                Log.Error("ERROR GetDataSet:" + ex.Message);
                throw new NotImplementedException(ex.Message);
            }
            finally {
                if (_autoClose && Connection.State != System.Data.ConnectionState.Closed) {
                    Connection.Close();
                }
            }
            return ds;
        }

        public void AddParameter(string name, DbType dbType, object val) {
            DbParameter dbParameter = _ProviderFactory.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = name;
            dbParameter.Value = val;
            Command.Parameters.Add(dbParameter);
        }

        public void ClearParameter() {
            Command.Parameters.Clear();
        }
    }
}
